﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;
public partial class BCDTXeBom : System.Web.UI.Page
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBCDTXeBom";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0,tiencabom = 0;
    static string ncc = "";
    static System.Data.DataTable dt;
    static System.Data.DataTable dtPrint;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            Title = "Báo cáo doanh thu theo mác hợp đồng | " + Header;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Chọn tháng", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");

            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;

            dlNam.Visible = true;
            
            LoadCaBom(dlLoaiCaBom);
        }
    }
    void LoadCaBom(DropDownList dl)
    {
        dl.Items.Clear();
        dl.Items.Insert(0, new ListItem("--Chọn loại ca bơm--", "0"));
        dl.SelectedIndex = 0;
    }
    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCDTTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCDTMacHopDong.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCDTBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(3))
            Response.Redirect("BCDTNVKD.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
            Response.Redirect("BCDTXeBom.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
            Response.Redirect("BCDTGiaVatLieu.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
            Response.Redirect("BCDTKhachHang.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(7))
            Response.Redirect("BCDTVatLieuTieuHao.aspx");
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex > 0)
        {
            if (f_SuDung.Equals(1))
            {
                CurrentPage = 1;
                btnNext.Enabled = true;
                Search(1);
            }
            else gstGetMess("Bạn không có quyền xem báo cáo này", "");
        }
        else gstGetMess("Chọn tháng cần xem", "");
    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    private void Search(int page)
    {
        int index = dlLoaiCaBom.SelectedIndex > 0 ? 1 : 2;
        

        getDate();
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiCaBom", int.Parse(dlLoaiCaBom.SelectedValue));
        p[2].SqlDbType = SqlDbType.Int;
        p[3] = new SqlParameter("@Index", index);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@PageNumber", page);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@RowspPage", PageNumber);
        p[5].SqlDbType = SqlDbType.Int;
        

        GV.DataSource = ex.GetData("sp_BCDT_XeBom", p);
        GV.DataBind();

        if (page == 1)
        {
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@Index", dlLoaiCaBom.SelectedIndex.Equals(0) ? 0 : int.Parse(dlLoaiCaBom.SelectedValue));
            p[2].SqlDbType = SqlDbType.Int;
            
            tiencabom = ex.GetValueFloat("sp_BCDT_XeBom_GetFooter", p);

        }
        if (GV.Rows.Count > 0)
        {
            GV.FooterRow.Cells[3].Text = "Tổng cộng";
            GV.FooterRow.Cells[3].HorizontalAlign = HorizontalAlign.Center;
            GV.FooterRow.Cells[9].Text = ex.ConvertDecimal(tiencabom);
        }
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            try
            {
                int index = dlLoaiCaBom.SelectedIndex > 0 ? 1 : 2;


                getDate();
                p = new SqlParameter[4];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@LoaiCaBom", int.Parse(dlLoaiCaBom.SelectedValue));
                p[2].SqlDbType = SqlDbType.Int;
                p[3] = new SqlParameter("@Index", index);
                p[3].SqlDbType = SqlDbType.Int;

                dtPrint = ex.GetData("sp_BCDT_XeBom_Print", p);
                PrintNPOI(dtPrint);
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");

            }
        }
        else
            gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();
        string ngaythang = "";
        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";

            if (dlLoaiCaBom.SelectedIndex > 0)
                sheetname = dlLoaiCaBom.SelectedItem.Text;

            else sheetname = "Bảng tổng hợp";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;

            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";
            
            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            string lcb = dlLoaiCaBom.SelectedIndex > 0 ? dlLoaiCaBom.SelectedItem.Text.ToUpper() : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("TỔNG HỢP DOANH THU CA BƠM " + lcb+  ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            if (dlLoaiCaBom.SelectedIndex > 0)
            {
                //loai ca bom
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue(dlLoaiCaBom.SelectedIndex > 0 ? "Loại ca bơm: " + dlLoaiCaBom.SelectedItem.Text : "");
                r1c1.CellStyle = hs3;
                r1c1.Row.Height = 500;
            }
            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2

            //header2
            rowIndex++;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất", "Biển số", "Khách hàng", "KL thực xuất", "Mác HĐ", "KL bán", "Loại đá","Loại ca bơm","Tiền ca bơm","NVKD","Hình thức HĐ","VAT" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 0; j < header.Length; j++)
            {
                cell = row.CreateCell(j + 1);
                cell.SetCellValue(header[j].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------
            //ghi du lieu tung dong
            int sodong = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                cell = row.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = hsCenter;

                for (int j = 1; j < dt.Columns.Count-1; j++)
                {
                    cell = row.CreateCell(j);

                    if (j == 4 || j == 6 || j == 9)
                    {
                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }
                        else
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        cell.CellStyle = hsRight;
                        
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString());

                        if (j ==1 || j == 5 || j == 7|| j==12)
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;


                }
                if (i > 0)
                {
                    if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][3].ToString().Equals(dt.Rows[i - 1][3].ToString()) && dt.Rows[i][8].ToString().Equals(dt.Rows[i - 1][8].ToString()) && dt.Rows[i][8].ToString().ToLower().Contains("ca"))
                    {
                        sodong++;
                        cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 9, 9);
                        sheet.AddMergedRegion(cra);
                    }
                    else
                        sodong = 0;
                }
            }

            #region Footer

            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(3);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            string[] footer = { "E","F","G","H","I","J"};
            for (int k = 4; k <= 6; k++)
            {
                if (k != 5)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "SUM(" + footer[k - 4] + "7:" + footer[k - 4] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }
            
            
            p = new SqlParameter[3];
            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
            p[0].SqlDbType = SqlDbType.Int;
            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
            p[1].SqlDbType = SqlDbType.Int;
            p[2] = new SqlParameter("@Index", dlLoaiCaBom.SelectedIndex.Equals(0) ? 0 : int.Parse(dlLoaiCaBom.SelectedValue));
            p[2].SqlDbType = SqlDbType.Int;

            cell = row.CreateCell(9);
            cell.SetCellValue(ex.GetValueFloat("sp_BCDT_XeBom_GetFooter", p));
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            for (int ik = 0; ik <= 12; ik++)
            {
                //fix lai border
                if (ik <= 2 || ik == 7 || ik == 8 || ik == 10 || ik == 11 || ik == 5 || ik==12)
                {
                    r1c1 = row.CreateCell(ik);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
                sheet.AutoSizeColumn(ik);
            }
            #endregion

            //end sheet tong hop--------------------------
            
            //sheet con
            if (dlLoaiCaBom.SelectedIndex.Equals(0))
            {

                int STT = 0;
                sodong = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        ncc = dt.Rows[i]["LoaiCaBom"].ToString();

                        if (!ncc.Equals(dt.Rows[i - 1]["LoaiCaBom"].ToString()))
                        {
                            //cabom = 0;
                            STT = 0;

                            #region Footer
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(3);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            string[] footer2 = { "E", "F", "G", "H", "I", "J" };
                            for (int k = 4; k <= 6; k++)
                            {
                                if (k != 5)
                                {
                                    cell = row.CreateCell(k);
                                    cell.CellFormula = "SUM(" + footer2[k - 4] + "7:" + footer2[k - 4] + rowIndex.ToString() + ")";
                                    cell.Row.Height = 500;
                                    cell.CellStyle = hs4;
                                }
                            }


                            p = new SqlParameter[3];
                            p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
                            p[0].SqlDbType = SqlDbType.Int;
                            p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
                            p[1].SqlDbType = SqlDbType.Int;
                            p[2] = new SqlParameter("@Index", int.Parse(dt.Rows[i - 1]["f_LoaiCaBom"].ToString()));
                            p[2].SqlDbType = SqlDbType.Int;

                            cell = row.CreateCell(9);
                            cell.SetCellValue(ex.GetValueFloat("sp_BCDT_XeBom_GetFooter", p));
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            for (int ik = 0; ik <= 12; ik++)
                            {
                                //fix lai border
                                if (ik <= 2 || ik == 7 || ik == 8 || ik == 10 || ik == 11 || ik == 5 || ik==12)
                                {
                                    r1c1 = row.CreateCell(ik);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                                sheet.AutoSizeColumn(ik);
                            }
                            #endregion

                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
                            
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP DOANH THU CA BƠM " + ncc.ToUpper() + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //loai ca bom
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Loại ca bơm: "+ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;

                                sheet.AutoSizeColumn(j);
                            }
                            #endregion
                            //End header-----------------------------------------------------------------------

                            STT++;

                        }
                    }
                    else
                    {
                        ncc = "";
                        if (!ncc.Equals(dt.Rows[i]["LoaiCaBom"].ToString()))
                        {
                            ncc = dt.Rows[i]["LoaiCaBom"].ToString();
                            sheet = workbook.CreateSheet(cl.EscapeSheetName(ncc));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP DOANH THU CA BƠM " + ncc.ToUpper() + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //loai ca bom
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Loại ca bơm: "+ncc);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 12);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header1-2

                            //header2
                            rowIndex++;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(0);
                            cell.SetCellValue("STT");
                            cell.CellStyle = hs4;
                            cell.Row.Height = 400;


                            //tao tieu de cot
                            for (int j = 0; j < header.Length; j++)
                            {
                                cell = row.CreateCell(j + 1);
                                cell.SetCellValue(header[j].ToString());
                                cell.CellStyle = hs4;
                                cell.Row.Height = 500;
                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= 12; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;


                    for (int j = 1; j < dt.Columns.Count-1; j++)
                    {
                        cell = row.CreateCell(j);

                        if (j == 4 || j == 6 || j == 9)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                            else
                                cell.SetCellValue(dt.Rows[i][j].ToString());
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());

                            if (j == 1 || j == 5 || j == 7||j==12)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }
                    if (i > 0)
                    {
                        if (dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()) && dt.Rows[i][3].ToString().Equals(dt.Rows[i - 1][3].ToString()) && dt.Rows[i][8].ToString().Equals(dt.Rows[i - 1][8].ToString()) && dt.Rows[i][8].ToString().ToLower().Contains("ca"))
                        {
                            sodong++;
                            cra = new CellRangeAddress(rowIndex - sodong, rowIndex, 9, 9);
                            sheet.AddMergedRegion(cra);
                        }
                        else
                            sodong = 0;
                    }

                    #region Footer
                    if (i == dt.Rows.Count - 1)
                    {
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(3);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        string[] footer2 = { "E", "F", "G", "H", "I", "J" };
                        for (int k = 4; k <= 6; k++)
                        {
                            if (k != 5)
                            {
                                cell = row.CreateCell(k);
                                cell.CellFormula = "SUM(" + footer2[k - 4] + "7:" + footer2[k - 4] + rowIndex.ToString() + ")";
                                cell.Row.Height = 500;
                                cell.CellStyle = hs4;
                            }
                        }
                        p = new SqlParameter[3];
                        p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
                        p[0].SqlDbType = SqlDbType.Int;
                        p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
                        p[1].SqlDbType = SqlDbType.Int;
                        p[2] = new SqlParameter("@Index", int.Parse(dt.Rows[i]["f_LoaiCaBom"].ToString()));
                        p[2].SqlDbType = SqlDbType.Int;

                        cell = row.CreateCell(9);
                        cell.SetCellValue(ex.GetValueFloat("sp_BCDT_XeBom_GetFooter", p));
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        for (int ik = 0; ik <= 12; ik++)
                        {
                            //fix lai border
                            if (ik <= 2 || ik == 7 || ik == 8 || ik == 10 || ik == 11 || ik == 5|| ik==12)
                            {
                                r1c1 = row.CreateCell(ik);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                }

                //Print tong san luong
                #region TongHop
                //Start header-----------------------------------------------------------------------
                #region Header
                //tao hnag dau tien
                sheet = workbook.CreateSheet("Tổng hợp sản lượng bơm");
                rowIndex = 0;

                //cong ty
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(0, 0, 0, 4);
                sheet.AddMergedRegion(cra);

                //chi nhanh
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                r1c1.CellStyle = hs1;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(1, 1, 0, 4);
                sheet.AddMergedRegion(cra);

                //tieu de bao cao
                ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;

                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                r1c1 = row.CreateCell(0);
                r1c1.SetCellValue("TỔNG HỢP SẢN LƯỢNG CA BƠM " + ngaythang);
                r1c1.CellStyle = hs2;
                r1c1.Row.Height = 500;

                cra = new CellRangeAddress(2, 2, 0, 12);
                sheet.AddMergedRegion(cra);

                rowIndex++;
                //freeze panes
                sheet.CreateFreezePane(0, 6);
                #endregion

                #region Header1-2

                //header2
                rowIndex++;
                rowIndex++;
                row = sheet.CreateRow(rowIndex);
                string[] header5 = { "Bơm theo ca", "Thành tiền", "Bơm theo khối", "Thành tiền", "Bơm thuê theo ca", "Thành tiền", "Bơm thuê theo khối", "Thành tiền", "Thuê bơm theo ca", "Thành tiền", "Thuê bơm theo khối", "Thành tiền", "Thành tiền" };


                //tao tieu de cot
                for (int j = 0; j < header5.Length; j++)
                {
                    cell = row.CreateCell(j);
                    cell.SetCellValue(header5[j].ToString());
                    cell.CellStyle = hs4;
                    cell.Row.Height = 500;
                }
                #endregion
                //End header-----------------------------------------------------------------------

                p = new SqlParameter[2];
                p[0] = new SqlParameter("@Thang", int.Parse(dlThang.SelectedValue));
                p[0].SqlDbType = SqlDbType.Int;
                p[1] = new SqlParameter("@Nam", int.Parse(dlNam.SelectedValue));
                p[1].SqlDbType = SqlDbType.Int;
                var tongsanluong = ex.GetData("sp_BCDT_XeBom_TongSanLuong", p);

                //ghi du lieu tung dong
                for (int i = 0; i < tongsanluong.Rows.Count; i++)
                {
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    for (int j = 0; j < tongsanluong.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        if (!tongsanluong.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(tongsanluong.Rows[i][j].ToString()));
                        }
                        else
                            cell.SetCellValue(tongsanluong.Rows[i][j].ToString());
                        cell.CellStyle = hsRight;
                    }

                }
                for (int ik = 0; ik <= tongsanluong.Columns.Count; ik++)
                {
                    sheet.AutoSizeColumn(ik);
                }
                #endregion
            }

            
        }
        using (var exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            string filename = dlLoaiCaBom.SelectedIndex > 0 ? dlLoaiCaBom.SelectedItem.Text : "";
            string saveAsFileName = "BCDT-CaBom-" + filename + " " + ngaythang + ".xls";

            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
            Response.Clear();
            Response.BinaryWrite(exportData.GetBuffer());
            Response.End();
        }
    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            //txtTuNgay.Visible = true;
            //txtDenNgay.Visible = true;
            //dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }
        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlLoaiCaBom.Items.Clear();
        dlLoaiCaBom.DataSource = ex.GetData("sp_BCDT_XeBom_LoadLoaiCaBom", p);
        dlLoaiCaBom.DataBind();
        dlLoaiCaBom.Items.Insert(0, new ListItem("--Chọn loại ca bơm--", "0"));
        dlLoaiCaBom.SelectedIndex = 0;

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        for (int rowIndex = GV.Rows.Count - 2; rowIndex >= 0; rowIndex--)
        {
            GridViewRow gvRow = GV.Rows[rowIndex];
            GridViewRow gvPreviousRow = GV.Rows[rowIndex + 1];

            if (gvRow.Cells[8].Text.Trim().ToLower().Contains("ca") && gvRow.Cells[1].Text.Equals(gvPreviousRow.Cells[1].Text) && gvRow.Cells[3].Text.Equals(gvPreviousRow.Cells[3].Text) && gvRow.Cells[8].Text.Equals(gvPreviousRow.Cells[8].Text) && gvRow.Cells[9].Text.Equals(gvPreviousRow.Cells[9].Text))
            {

                if (gvPreviousRow.Cells[1].RowSpan < 2)
                {
                    gvRow.Cells[1].RowSpan = 2;
                    gvRow.Cells[3].RowSpan = 2;
                    gvRow.Cells[8].RowSpan = 2;
                    gvRow.Cells[9].RowSpan = 2;
                }
                else
                {
                    gvRow.Cells[1].RowSpan = gvPreviousRow.Cells[1].RowSpan + 1;
                    gvRow.Cells[3].RowSpan = gvPreviousRow.Cells[3].RowSpan + 1;
                    gvRow.Cells[8].RowSpan = gvPreviousRow.Cells[8].RowSpan + 1;
                    gvRow.Cells[9].RowSpan = gvPreviousRow.Cells[9].RowSpan + 1;
                }
                gvPreviousRow.Cells[1].Visible = false;
                gvPreviousRow.Cells[3].Visible = false;
                gvPreviousRow.Cells[8].Visible = false;
                gvPreviousRow.Cells[9].Visible = false;
            }
        }
    }
    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
}